package com.fengxing.ams.web.facade.mouble.score;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

public interface IScoreDAO extends PagingAndSortingRepository<ScoreVO, Integer>, JpaSpecificationExecutor<ScoreVO>  {

	@Query(value="SELECT id, score, ranking, SUBJECT, ABS((score - :score)) AS YEAR  FROM zyt_ranking WHERE SUBJECT=:subject AND YEAR=:year ORDER BY YEAR limit 0, 1", nativeQuery=true)
	ScoreVO findOne(@Param("year")Integer year, @Param("score")Integer score, @Param("subject")String subject);
	
	@Query(value="SELECT id,score,ranking,SUBJECT, ABS(ranking - :ranking) AS YEAR FROM zyt_ranking WHERE SUBJECT=:subject AND  YEAR=:year ORDER BY YEAR LIMIT 0,1 ", nativeQuery=true)
	ScoreVO queryRankingByRanking(@Param("year")Integer year, @Param("ranking")Integer ranking,  @Param("subject")String subject);
	
	@Query(value="SELECT * FROM zyt_ranking a, (SELECT ranking  FROM zyt_ranking WHERE SUBJECT=:subject AND YEAR=:formYear ORDER BY ABS((score - :score)) LIMIT 0, 1) b WHERE a.subject=:subject AND YEAR=:toYear ORDER BY ABS(a.ranking - b.ranking) LIMIT 0,1", nativeQuery=true)
	ScoreVO queryRankingByScoreSubjectYear(@Param("formYear")Integer formYear, @Param("toYear")Integer toYear, 
			@Param("score")Integer score, @Param("subject")String subject);
}
